/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.nc.kyiv.yellow.sociallibrary.dao;

import com.nc.kyiv.yellow.sociallibrary.ConnectionManager.ConnectionManager;
import com.nc.kyiv.yellow.sociallibrary.specificDaoInterface.IBookDAO;
import com.nc.kyiv.yellow.sociallibrary.POJO.*;
import com.nc.kyiv.yellow.sociallibrary.constants.DataBaseConstants;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 *
 * @author My
 */
public class BookDAO implements IBookDAO {

    @Override //Valerie Avramenko
    //search in our data base
    public Book getByISBN(String isbn) throws Exception {
        Connection conn = null;
        Book book = null;
        String query =
                "BEGIN"
                + "SELECT isbn, title, description, page_number, image, "
                + "COUNT(rating.RATING) AS sum_rating, AVG(rating.rating) AS avg_rating"
                + "FROM book LEFT JOIN RATING ON book.ID = RATING.BOOK_ID"
                + "WHERE isbn = ?"
                + "GROUP BY isbn, title, description, page_number, image;"
                + "END;";
        try {
            conn = ConnectionManager.getConnection();
            PreparedStatement preparedStatement = conn.prepareStatement(query);
            preparedStatement.setString(1, isbn);
            ResultSet rs = preparedStatement.executeQuery();
            if (rs.next()) {
                book = new Book(isbn, rs.getString("title"), rs.getString("descriprion"),
                        rs.getInt("page_number"), rs.getString("image"));
                //adding authors
                List<BookAuthor> listAuthors = null;
                String author_query = "BEGIN"
                        + "SELECT first_name, last_name, middle_name "
                        + "FROM author"
                        + "WHERE id = (SELECT author_id FROM book_author "
                        + "WHERE book_id = ?);"
                        + "END;";
                PreparedStatement preparedStatementAuthor = conn.prepareStatement(author_query);
                preparedStatementAuthor.setInt(1, rs.getInt("id"));
                ResultSet rs_author = preparedStatementAuthor.executeQuery();
                while (rs_author.next()) {
                    //creating list of authors
                    String firstName = rs_author.getString("first_name");
                    String middleName = rs_author.getString("middle_name");
                    String lastName = rs_author.getString("last_name");
                    BookAuthor author = new BookAuthor(firstName, middleName, lastName);
                    listAuthors.add(author);
                }
                book.setAuthors(listAuthors);

                //adding rating
                List<BookRating> listRating = null;
                String rating_query = "SELECT rating FROM rating WHERE book_id = ?";
                PreparedStatement preparedStatementRating = conn.prepareStatement(rating_query);
                preparedStatementRating.setInt(1, rs.getInt("id"));
                ResultSet rs_rating = preparedStatementRating.executeQuery();
                while (rs_rating.next()) {
                    int score = rs_rating.getInt("rating"); //value of score
                    int count = 1; //initial amount
                    BookRating rating = new BookRating(score, count);
                    //counting the amount of each score
                    for (int i = 0; i < listRating.size() - 1; i++) {
                        //if score was already mentioned, increase counter
                        if (rating.getRatingScore() == listRating.get(i).getRatingScore()) {
                            listRating.get(i).setRatingScore(listRating.get(i).getRatingScore() + 1);
                        } else { //if unique, add to list
                            listRating.add(rating);
                        }
                    }
                }
                book.setRating(listRating);

                //adding genres
                List<String> genres = null;
                String genres_query = "BEGIN"
                        + "SELECT genre FROM genre"
                        + "WHERE id = (SELECT genre_id FROM book_genre"
                        + "WHERE book_id = ?);"
                        + "END";
                PreparedStatement preparedStatementGenre = conn.prepareStatement(genres_query);
                preparedStatementGenre.setInt(1, rs.getInt("id"));
                ResultSet rs_genres = preparedStatementGenre.executeQuery();
                List<String> listGenres = null;
                while (rs_genres.next()) {
                    listGenres.add(rs_genres.getString("genre"));
                }
                book.setGenres(listGenres);
            } else {
                book = null;
            }
        } catch (SQLException ex) {
            throw new Exception("Something is wrong with Data Base");
        } finally {
            if (conn != null) {
                ConnectionManager.releaseConnection(conn);
            }
        }
        return book;
    }

    @Override
    public void save(Book book) throws Exception {
        Connection conn = null;
        PreparedStatement preparedStatementInsertBookGenre = null;
        PreparedStatement preparedStatementInsertBook = null;
        PreparedStatement preparedStatementInsertLocalLib = null;

        String moderStatus;
        if (book.getTemporaryUserID() == null) {
            moderStatus = "in progress";
        } else {
            moderStatus = "new";
        }

        String queryInsertBook = "INSERT INTO book (isbn, title, description, page_number, image, published_by, regist_date, moder_status) "
                + "VALUES("
                + "?, ?, ?, ?, ?, ?, " //isbn, title, description, page_number, image, published_by
                + "TO_DATE(sysdate, 'DD-MM-RRRR'),"
                + "(SELECT id FROM moder_status WHERE status = '" + moderStatus + "'))";

        String queryInsertBookGenre = "INSERT INTO book_genre (book_id, genre_id) "
                + "VALUES("
                + "(SELECT id FROM book WHERE isbn = ?), "
                + "(SELECT id FROM genre WHERE genre = ?))";

        String queryInsertLocalLib = "INSERT INTO local_lib (user_id, book_id, read_status) "
                + "VALUES("
                + "?, " //user_id
                + "(SELECT id FROM book WHERE isbn = ?)," //isbn
                + "(SELECT id FROM read_status WHERE status = 'new'))";

        String queryInsertBook_Author = "INSERT INTO book_author (book_id, author_id) "
                + "VALUES("
                + "?, " //user_id
                + "(SELECT id FROM book WHERE isbn = ?)," //isbn
                + "(SELECT id FROM read_status WHERE status = 'new'))";

        String queryInsertAuthors = "INSERT INTO book_author (book_id, author_id) "
                + "VALUES("
                + "?, " //user_id
                + "(SELECT id FROM book WHERE isbn = ?)," //isbn
                + "(SELECT id FROM read_status WHERE status = 'new'))";

        try {
            conn = ConnectionManager.getConnection();
            conn.setAutoCommit(false);
            preparedStatementInsertBook = conn.prepareStatement(queryInsertBook.toString());
            preparedStatementInsertBook.setString(1, book.getISBN());
            preparedStatementInsertBook.setString(2, book.getTitle());
            preparedStatementInsertBook.setString(3, book.getDescription());
            preparedStatementInsertBook.setInt(4, book.getPageNumber());
            preparedStatementInsertBook.setString(5, book.getImageURL());
            if (book.getTemporaryUserID() != null) {
                preparedStatementInsertBook.setInt(6, Integer.parseInt(book.getTemporaryUserID()));
            } else {
                preparedStatementInsertBook.setNull(6, java.sql.Types.NULL);
            }
            preparedStatementInsertBookGenre = conn.prepareStatement(queryInsertBookGenre.toString());
            preparedStatementInsertBookGenre.setString(1, book.getISBN());
            preparedStatementInsertBookGenre.setString(2, book.getGenre());

            preparedStatementInsertBook.execute();
            preparedStatementInsertBookGenre.execute();
            if (book.getTemporaryUserID() != null) {
                preparedStatementInsertLocalLib = conn.prepareStatement(queryInsertLocalLib.toString());
                preparedStatementInsertLocalLib.setInt(1, Integer.parseInt(book.getTemporaryUserID()));
                preparedStatementInsertLocalLib.setString(2, book.getISBN());
                preparedStatementInsertLocalLib.execute();

            }
            conn.commit();
            System.out.println("Commited");
        } catch (SQLException ex) {
            System.out.println("catch block");
            if (conn != null) {
                try {
                    System.out.print("Transaction is being rolled back. Error: " + ex.getMessage());
                    conn.rollback();
                } catch (SQLException ex1) {
                    System.out.println("Error: " + ex1.getMessage());
                }
            }
            if (ex.getMessage().indexOf("unique constraint") != -1) {
                throw new Exception("This book is already added");
            } else {
                throw new Exception("Something is wrong with Data Base");
            }
        } finally {
            if (conn != null) {
                conn.setAutoCommit(true);
                ConnectionManager.releaseConnection(conn);
                preparedStatementInsertBook.close();
                preparedStatementInsertBookGenre.close();
                if (preparedStatementInsertLocalLib != null) {
                    preparedStatementInsertLocalLib.close();
                }
            }
        }
    }

    @Override //Valerie Avramenko
    //for admin - deleting from DB
    public void delete(Book book) throws Exception {
        Connection conn = null;
        //creating needed SQL-statement
        String query = ""
                + "DECLARE b_title book.title%type = ?"
                + "BEGIN"
                + "DELETE FROM book_author"
                + "WHERE book_id = (SELECT id FROM book WHERE title = b_title);"
                + "DELETE FROM book_genre"
                + "WHERE book_id = (SELECT id FROM book WHERE title = b_title);"
                + "DELETE FROM local_lib"
                + "WHERE book_id = (SELECT id FROM book WHERE title = b_title);"
                + "DELETE FROM rating"
                + "WHERE book_id = (SELECT id FROM book WHERE title = b_title);"
                + "DELETE FROM book"
                + "WHERE id = (SELECT id FROM book WHERE title = b_title);"
                + "END;";

        //getting a connection and executing prepared statement
        try {
            conn = ConnectionManager.getConnection();
            conn.setAutoCommit(false);
            PreparedStatement preparedStatement = conn.prepareStatement(query.toString());
            preparedStatement.setString(1, book.getTitle());
            preparedStatement.execute();
            conn.commit();
        } catch (SQLException ex) {
            throw new Exception("Something is wrong with Data Base");
        } finally {
            if (conn != null) {
                ConnectionManager.releaseConnection(conn);
            }
        }
    }

    //Evgeniy Grigorevskiy
    public void deleteFromLocal(Map parameters) throws Exception {

        Connection conn = null;
        CallableStatement cStmt = null;
        int userId = (int) Integer.parseInt(parameters.get("userId").toString());
        String isbn = parameters.get("isbn").toString();

        try {
            conn = ConnectionManager.getConnection();

            cStmt = conn.prepareCall("{call DELETE_BOOK_FROM_LOCAL_LIBRARY(?,?)}");
            cStmt.setInt("input_userID", userId);
            cStmt.setString("input_bookISBN", isbn);

            cStmt.execute();

        } catch (SQLException ex) {
            throw new Exception("Something  wrong");
        } finally {
            try {
                if (cStmt != null) {
                    cStmt.close();
                }
            } finally {
                if (conn != null) {
                    ConnectionManager.releaseConnection(conn);
                }
            }
        }
    }

    //created by Evgeniy Grigorevskiy
    public void addToLocalLibrary(Map parameters) throws Exception {

        Connection conn = null;
        CallableStatement cStmt = null;
        int userId = (int) Integer.parseInt(parameters.get("userId").toString());
        String isbn = parameters.get("isbn").toString();

        try {
            conn = ConnectionManager.getConnection();

            cStmt = conn.prepareCall("{call ADD_BOOK_TO_LOCAL_FROM_GLOBAL(?,?)}");
            cStmt.setInt("input_userID", userId);
            cStmt.setString("input_bookISBN", isbn);

            cStmt.execute();

        } catch (SQLException ex) {
            throw new Exception("Something  wrong");
        } finally {
            try {
                if (cStmt != null) {
                    cStmt.close();
                }
            } finally {
                if (conn != null) {
                    ConnectionManager.releaseConnection(conn);
                }
            }
        }
    }

    @Override
    public void update(Book book) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    //created by Alena
    public String setOrUpdateRating(Map<String, String> params) throws SQLException, Exception {

        Connection conn = null;
        CallableStatement cStmt = null;
        int userId = (int) Integer.parseInt(params.get("userId"));
        String isbn = params.get("isbn");
        int score = (int) Integer.parseInt(params.get("rating"));
        try {
            conn = ConnectionManager.getConnection();

            System.out.println("in rating dao");

            cStmt = conn.prepareCall("{call SET_BOOK_RATING(?,?,?)}");
            cStmt.setInt("input_user_id", userId);
            cStmt.setString("input_isbn", isbn);
            cStmt.setInt("input_rating", score);

            cStmt.execute();

            return "success";

        } catch (SQLException ex) {
            throw new Exception("Something is wrong with Data Base");
        } finally {
            try {
                if (cStmt != null) {
                    cStmt.close();
                }
            } finally {
                if (conn != null) {
                    ConnectionManager.releaseConnection(conn);
                }
            }
        }
    }

    //created by Alena
    public String setReadStatus(Map<String, String> params) throws SQLException, Exception {

        Map readStatusMap = DataBaseConstants.getReadStatus();
        Connection conn = null;
        CallableStatement cStmt = null;
        int userId = (int) Integer.parseInt(params.get("userId"));
        String isbn = params.get("isbn");  
        int readStatus = (int) readStatusMap.get(params.get("readStatus"));
        
        System.out.println(readStatus);
        System.out.println(userId);
        System.out.println(isbn);
        
        try {
            conn = ConnectionManager.getConnection();

            cStmt = conn.prepareCall("{call SET_READ_STATUS(?,?,?)}");
            cStmt.setInt("input_user_id", userId);
            cStmt.setString("input_isbn", isbn);
            cStmt.setInt("input_status", readStatus);

            cStmt.execute();

            return "sucssess";

        } catch (SQLException ex) {
            throw new Exception("Something is wrong with Data Base");
        } finally {
            try {
                if (cStmt != null) {
                    cStmt.close();
                }
            } finally {
                if (conn != null) {
                    ConnectionManager.releaseConnection(conn);
                }
            }
        }
    }

    //created by Alena
    public String publishToGlobalLib(Map<String, String> params) throws SQLException, Exception {

        Connection conn = null;
        CallableStatement cStmt = null;
        int userId = (int) Integer.parseInt(params.get("userId"));
        String isbn = params.get("isbn");
        System.out.println(isbn);
        System.out.println(userId);
        try {
            conn = ConnectionManager.getConnection();

            cStmt = conn.prepareCall("{call PUBLISH_TO_GLOBAL_LIB(?,?)}");
            cStmt.setInt("input_user_id", userId);
            cStmt.setString("input_isbn", isbn);

            cStmt.execute();

            return "sucssess";

        } catch (SQLException ex) {
            if (ex.getMessage().equals("That book is already published or it is being moderated now")) {
                System.out.println(ex.getMessage());
                throw ex;
            }
            throw new Exception("Something is wrong with Data Base");
        } finally {
            try {
                if (cStmt != null) {
                    cStmt.close();
                }
            } finally {
                if (conn != null) {
                    ConnectionManager.releaseConnection(conn);
                }
            }
        }
    }

    //created by Alena
    public String setModerStatus(Map<String, String> params) throws SQLException, Exception {

        Map moderStatusMap = DataBaseConstants.getModerStatus();        
        Connection conn = null;
        CallableStatement cStmt = null;
        String isbn = params.get("isbn");
        int moderStatus = (int) moderStatusMap.get(params.get("moderStatus"));
        
        System.out.println(isbn);
        System.out.println(moderStatus);
        
        try {
            conn = ConnectionManager.getConnection();

            cStmt = conn.prepareCall("{call SET_MODER_STATUS(?,?)}");
            cStmt.setString("input_isbn", isbn);
            cStmt.setInt("input_status", moderStatus);

            cStmt.execute();

            return "sucssess";

        } catch (SQLException ex) {
            throw new Exception("Something is wrong with Data Base");
        } finally {
            try {
                if (cStmt != null) {
                    cStmt.close();
                }
            } finally {
                if (conn != null) {
                    ConnectionManager.releaseConnection(conn);
                }
            }
        }
    }
}
